create language plpgsql;

-- Current timestamp in Europe/Moscow timezone
create function public.msk_now() returns timestamp as $BODY$
	select now() at time zone 'Europe/Moscow';
$BODY$ language sql immutable;

-- Age with current timestamp in Europe/Moscow timezone
-- @param timestamp
create function public.msk_age(timestamp with time zone) returns interval as $BODY$
	select age(msk_now(), $1);
$BODY$ language sql immutable;

-- Make secret hash
-- @param user ID
-- @param registration date
-- @param password
create function public.make_secret(uuid, timestamp with time zone, text) returns varchar as $BODY$
begin
	return md5($1 || md5(cast($2 as varchar)) || $3);
end;
$BODY$ language plpgsql immutable;

-- Register user
-- @param email
-- @param username
-- @param secret
create function public.create_account(
					i_email text,
					i_username text,
					i_password text,
					OUT user_id uuid,
					OUT shash text) as $BODY$
declare
	person public.users%ROWTYPE;
	secret_hash text;
begin
	-- try insert a user
	insert into users(email, username, secret) values(i_email, i_username, i_password)
		returning
			* into strict person;

	-- update password
	update users set secret = make_secret(id, date_reg, secret) where id = person.id
		returning
			secret into secret_hash;

	-------------------------------------------------- ouput
	-- user ID
	user_id := person.id;
	-- secret hash for email accepting
	shash := make_session_hash(person.date_reg, secret_hash, secret_hash);
end;
$BODY$ language plpgsql volatile;

-- Create a session hash
-- @param registration date
-- @param user password hash
-- @param password salt
create function public.make_session_hash(timestamp with time zone, text, text) returns varchar as $BODY$
begin
	return md5(extract(microseconds from $1) || $2 || $3);
end;
$BODY$ language plpgsql immutable;

-- Create a user session
-- @param user ID
-- @param password salt
create function public.generate_user_session(
					i_id uuid,
					i_salt text,
					OUT name text,
					OUT hash text) as $BODY$
declare
	person public.users%ROWTYPE; -- user bean
begin
	-- fetch a user
	select * into strict person from users where id = i_id;

	name := person.username;
	hash := make_session_hash(person.date_reg, person.secret, i_salt);
end;
$BODY$ language plpgsql immutable;

-- Activate account by ID
-- @param user ID
-- @param timeout
create function public.activate_id(
					i_id uuid,
					i_salt text,
					i_ttl interval,
					OUT name text,
					OUT hash text) as $BODY$
declare
	person public.users%ROWTYPE; -- user bean
begin
	-- fetch a user
	select * into strict person from users
		where
			id = i_id
			and date_reg = last_visit
			and msk_age(date_reg) < i_ttl;

	name := person.username;
	hash := make_session_hash(person.date_reg, person.secret, i_salt);
end;
$BODY$ language plpgsql immutable;

-- Activate account E-mail
-- @param user ID
-- @param secret hash
-- @param timeout
create function public.activate_email(
					i_id uuid,
					i_salt text,
					i_hash text,
					i_ttl interval,
					OUT name text,
					OUT hash text) as $BODY$
declare
	person public.users%ROWTYPE; -- user bean
begin
	-- fetch a user
	select * into strict person from users
		where
			id = i_id
			and locked = 0
			and msk_age(date_reg) < i_ttl
			and make_session_hash(date_reg, secret, secret) = i_hash;

	-- update lock flag
	update users set locked = 1 where id = i_id;

	name := person.username;
	hash := make_session_hash(person.date_reg, person.secret, i_salt);
end;
$BODY$ language plpgsql volatile;

-- Validate a user
-- @param column to confirm
-- @param value to confirm
-- @param password
-- @param timeout for untrusted email
-- @param password salt
create function public.validate_user(
					i_column text,
					i_value text,
					i_password text,
					i_timeout interval,
					i_salt text,
					OUT user_id uuid,
					OUT name text,
					OUT hash text) as $BODY$
declare
	query text;
	person public.users%ROWTYPE; -- user bean
begin
	query := 'SELECT * FROM PUBLIC.users WHERE ' || quote_ident(i_column) || ' = ' || quote_literal(i_value) ||
			-- trust email OR waiting for it
			' AND (locked = 1 OR locked = 0 AND msk_age(date_reg) < INTERVAL ' || quote_literal(i_timeout) || ')' ||
			' AND secret = make_secret(id, date_reg, ' || quote_literal(i_password) || ')';

	execute query into strict person;

	user_id := person.id;
	name := person.username;
	hash := make_session_hash(person.date_reg, person.secret, i_salt);
end;
$BODY$ language plpgsql immutable;

-- Fetch a person
-- @param username
-- @param timeout for untrust email
-- @param session ID
create function public.person(text, interval, uuid) returns person_type as $BODY$

	-- update last visit for session ID
	update users set last_visit = msk_now() where id = $3;

	-- fetch a person
	select
		u.id, email, username, nickname, fullname, dob, gender, postcode, country, lang, timezone, date_reg, last_visit, -- user
		frmt, path -- avatar
	from
		users as u left join avatars as a on u.id = a.id
	where
		username = $1
		and (locked = 1 or locked = 0 and msk_age(date_reg) < $2);

$BODY$ language sql volatile;

-- Fetch person volatile columns
-- @param user ID
create function public.person_volatile(uuid) returns person_volatile_type as $BODY$

	select
		nickname, fullname, dob, gender, postcode, country, lang, timezone,
		frmt, path -- avatar
	from
		users as u left join avatars as a on u.id = a.id
	where
		u.id = $1;

$BODY$ language sql immutable;

-- Save user picture
-- @param user id
-- @param frmt
-- @param load date
-- @param source
create function public.set_user_pic(uuid, integer, integer, bytea) returns varchar as $BODY$
declare
	defined boolean default false;
	picpath varchar;
begin

	select true into defined from avatars where id = $1;

	picpath := md5($1 || cast($3 as varchar));

	if defined then
		update avatars set frmt = $2, load_date = $3, pic = $4, path = picpath where id = $1;
	else
		-- id, pic, frmt, load_date, path
		insert into avatars values($1, $4, $2, $3, picpath);
	end if;

	return picpath;

end;
$BODY$ language plpgsql volatile;

-- Fetch a user picture
create function public.get_user_pic(
					i_path text,
					i_frmt integer,
					i_date integer,
					OUT state integer,
					OUT pic_date integer,
					OUT pic bytea) as $BODY$
declare
	avatar public.avatars%ROWTYPE;
begin

	-- fetch a record
	select * into avatar from avatars where path = i_path and frmt = i_frmt;

	-- defaults
	state := 0;
	pic := NULL;
	pic_date := NULL;

	if not found then
		state := -1;
	elseif avatar.load_date = i_date then
		state := 1;
	else
		pic := avatar.pic;
		pic_date := avatar.load_date;
	end if;

end;
$BODY$ language plpgsql immutable;

-------------------------------------------------------------------------------------- end of file